﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DALHelper.Business;

namespace DALHelper.DAL
{
    public class TransactionDAL
    {
        private SqlConnection m_connection;

        public TransactionDAL(SqlConnection conn)
        {
            m_connection = conn;
        }

        public void AddTransaction(Transaction t)
        {
            m_connection.Open();

            SqlCommand cmd = new SqlCommand("sp_ThemGiaoDich", m_connection);
            cmd.CommandType = CommandType.StoredProcedure;
            
            SqlParameter para;

            para = new SqlParameter("TruongDoan", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = t.Representer;
            cmd.Parameters.Add(para);
            
            para = new SqlParameter("BatDau", System.Data.SqlDbType.DateTime);
            para.Direction = ParameterDirection.Input;
            para.Value = t.StartTime;
            cmd.Parameters.Add(para);

            para = new SqlParameter("KetThuc", System.Data.SqlDbType.DateTime);
            para.Direction = ParameterDirection.Input;
            para.Value = t.EndTime;
            cmd.Parameters.Add(para);

            if (t.UserName == "")
                t.UserName = t.Representer;
            para = new SqlParameter("TenDangNhap", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = t.UserName;
            cmd.Parameters.Add(para);

            if (t.Password == "")
                t.Password = createPass();
            para = new SqlParameter("MatKhau", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = t.Password;
            cmd.Parameters.Add(para);

            para = new SqlParameter("Wait", System.Data.SqlDbType.Bit);
            para.Direction = ParameterDirection.Input;
            para.Value = 0;
            cmd.Parameters.Add(para);

            try
            {
                long id = long.Parse(cmd.ExecuteScalar().ToString());
                t.TransactionID = id;
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                m_connection.Close();
            }
        }

        public void UpdateTransaction(Transaction t)
        {
            m_connection.Open();

            SqlCommand cmd = new SqlCommand("sp_CapNhatGiaoDich", m_connection);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter para;

            string id = "000" + t.TransactionID;
            id = "GD" + id.Substring(id.Length - 3);
            para = new SqlParameter("MaDoan", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = id;
            cmd.Parameters.Add(para);

            para = new SqlParameter("TruongDoan", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = t.Representer;
            cmd.Parameters.Add(para);

            para = new SqlParameter("BatDau", System.Data.SqlDbType.DateTime);
            para.Direction = ParameterDirection.Input;
            para.Value = t.StartTime;
            cmd.Parameters.Add(para);

            para = new SqlParameter("KetThuc", System.Data.SqlDbType.DateTime);
            para.Direction = ParameterDirection.Input;
            para.Value = t.EndTime;
            cmd.Parameters.Add(para);

            para = new SqlParameter("TenDangNhap", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = t.UserName;
            cmd.Parameters.Add(para);

            para = new SqlParameter("MatKhau", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = t.Password;
            cmd.Parameters.Add(para);

            para = new SqlParameter("Wait", System.Data.SqlDbType.Bit);
            para.Direction = ParameterDirection.Input;
            para.Value = 0;
            cmd.Parameters.Add(para);

            try
            {
                int result = cmd.ExecuteNonQuery();
                if (result == 0)
                {
                    cmd.CommandText = "select * from GIAO_DICH where MA_DOAN = '" + id + "'";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Clear();
                    SqlDataReader dt = cmd.ExecuteReader(CommandBehavior.SingleRow);

                    while (dt.Read())
                    {
                        t.Representer = dt["DAI_DIEN"].ToString();
                        t.StartTime = DateTime.Parse(dt["BAT_DAU"].ToString());
                        t.EndTime = DateTime.Parse(dt["KET_THUC"].ToString());
                        t.UserName = dt["TEN_DANG_NHAP"].ToString();
                        t.Password = dt["MAT_KHAU"].ToString();
                    }
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                m_connection.Close();
            }
        }

        public void RemoveTransaction(long lTransID)
        {
            m_connection.Open();

            SqlCommand cmd = new SqlCommand("sp_XoaGiaoDich", m_connection);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter para;

            string id = "000" + lTransID;
            id = "GD" + id.Substring(id.Length - 3);
            para = new SqlParameter("MaDoan", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = id;
            cmd.Parameters.Add(para);

            para = new SqlParameter("Wait", System.Data.SqlDbType.Bit);
            para.Direction = ParameterDirection.Input;
            para.Value = 0;
            cmd.Parameters.Add(para);

            try
            {
                int result = cmd.ExecuteNonQuery();
                if (result == 0)
                {
                    throw new Exception("Xoa giao dich khong thanh cong!");
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                m_connection.Close();
            }
        }

        public Transaction QueryTransaction(long lTrans)
        {
            m_connection.Open();

            SqlCommand cmd = new SqlCommand("sp_LayThongTinGiaoDich", m_connection);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter para;

            string id = "000" + lTrans;
            id = "GD" + id.Substring(id.Length - 3);
            para = new SqlParameter("ID", System.Data.SqlDbType.VarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = id;
            cmd.Parameters.Add(para);

            para = new SqlParameter("DaiDien", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = "";
            cmd.Parameters.Add(para);

            para = new SqlParameter("Wait", System.Data.SqlDbType.Bit);
            para.Direction = ParameterDirection.Input;
            para.Value = 0;
            cmd.Parameters.Add(para);

            List<Transaction> lst = new List<Transaction>();
            SqlDataReader result;

            try
            {
                result = cmd.ExecuteReader();

                while (result.Read())
                {
                    Transaction t = new Transaction();
                    t.TransactionID = long.Parse(result["MA_DOAN"].ToString().Substring(2));
                    t.Representer = (string)result["DAI_DIEN"];
                    t.GuestCount = (int)result["SO_NGUOI"];
                    t.RoomCount = (int)result["SO_PHONG"];
                    t.StartTime = (DateTime)result["BAT_DAU"];
                    t.EndTime = (DateTime)result["KET_THUC"];
                    t.TotalCost = (decimal)result["TONG_TIEN"];
                    t.UserName = (string)result["TEN_DANG_NHAP"];
                    t.Password = (string)result["MAT_KHAU"];
                    lst.Add(t);
                }
                return lst[0];
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                m_connection.Close();
            }
        }

        public IEnumerable<Transaction> GetInfoTrans(string sPresenter)
        {
            m_connection.Open();

            SqlCommand cmd = new SqlCommand("sp_LayThongTinGiaoDich", m_connection);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter para;

            para = new SqlParameter("ID", System.Data.SqlDbType.VarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = "";
            cmd.Parameters.Add(para);

            para = new SqlParameter("DaiDien", System.Data.SqlDbType.NVarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = sPresenter;
            cmd.Parameters.Add(para);

            para = new SqlParameter("Wait", System.Data.SqlDbType.Bit);
            para.Direction = ParameterDirection.Input;
            para.Value = 0;
            cmd.Parameters.Add(para);

            List<Transaction> lst = new List<Transaction>();
            SqlDataReader result;

            try
            {
                result = cmd.ExecuteReader();

                while (result.Read())
                {
                    Transaction t = new Transaction();
                    t.TransactionID = long.Parse(result["MA_DOAN"].ToString().Substring(2));
                    t.Representer = (string)result["DAI_DIEN"];
                    t.GuestCount = (int)result["SO_NGUOI"];
                    t.RoomCount = (int)result["SO_PHONG"];
                    t.StartTime = (DateTime)result["BAT_DAU"];
                    t.EndTime = (DateTime)result["KET_THUC"];
                    t.TotalCost = (decimal)result["TONG_TIEN"];
                    t.UserName = (string)result["TEN_DANG_NHAP"];
                    t.Password = (string)result["MAT_KHAU"];
                    lst.Add(t);
                }
                return lst;
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                m_connection.Close();
            }
        }

        private string createPass()
        {
            string s1 = "", s2 = "";
            Random r = new Random(1234567890);

            for (int i = 0; i < 5; i++)
            {
                s1 += (char)r.Next(97, 122);
                s2 += r.Next(0, 9).ToString();
            }

            return s1 + s2;
        }

        public IEnumerable<TransDetail> QueryTransactionDetail(long lTrans)
        {
            m_connection.Open();

            SqlCommand cmd = new SqlCommand("sp_LayChiTietGiaoDich", m_connection);
            cmd.CommandType = CommandType.StoredProcedure;

            SqlParameter para;

            string id = "000" + lTrans;
            id = "GD" + id.Substring(id.Length - 3);
            para = new SqlParameter("ID", System.Data.SqlDbType.VarChar);
            para.Direction = ParameterDirection.Input;
            para.Value = id;
            cmd.Parameters.Add(para);

            para = new SqlParameter("Wait", System.Data.SqlDbType.Bit);
            para.Direction = ParameterDirection.Input;
            para.Value = 0;
            cmd.Parameters.Add(para);

            List<TransDetail> lst = new List<TransDetail>();
            SqlDataReader result;

            try
            {
                result = cmd.ExecuteReader();

                while (result.Read())
                {
                    TransDetail t = new TransDetail();
                    
                    t.TransactionID = (string)result["MA_DOAN"];
                    t.TransDetailID = (string)result["MA_CHI_TIET"];
                    t.RoomID = (string)result["MA_PHONG"];
                    t.TokenID = (result["MA_THE"] as string) == null ? string.Empty : result["MA_THE"] as string;
                    t.StartTime = (DateTime)result["BAT_DAU"];
                    t.EndTime = (DateTime)result["KET_THUC"];
                    t.RoomFee = (decimal)result["DON_GIA"];
                    t.CompensationFee = (decimal)result["BOI_THUONG"];
                    t.RoomCost = (decimal)result["THANH_TIEN"];
                    t.GuestName = (result["CA_NHAN"] as string) == null ? string.Empty : result["CA_NHAN"] as string;
                    t.GuestIDNumber = (result["CMND"] as string) == null ? string.Empty : result["CMND"] as string;
                    lst.Add(t);
                }
                return lst;
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                m_connection.Close();
            }
        }
    }
}
